﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class AuditTrail
    {
        public AuditTrail()
        { 
        }

        public List<AuditTrailInfo> GetList(string TableName, DateTime StartDate, DateTime EndDate)
        {
            List<AuditTrailInfo> list = new List<AuditTrailInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT ACT_TYPE, TABLE_NAME, PRIMARY_KEY, FIELD_NAME, ISNULL(OLD_VALUE,'') AS OLD_VALUE, ISNULL(NEW_VALUE,'') AS NEW_VALUE, UPDATE_BY, UPDATE_DATE, DB_USER_NAME
                            FROM TBL_AUDIT WHERE (ACT_TYPE <> 'X') AND (TABLE_NAME LIKE '' + @PARAM + '') AND (UPDATE_DATE BETWEEN @START_DATE AND @END_DATE) ORDER BY UPDATE_DATE DESC");
            db.AddInParameter(cmd, "@PARAM", DbType.String, TableName);
            db.AddInParameter(cmd, "@START_DATE", DbType.DateTime, StartDate);
            db.AddInParameter(cmd, "@END_DATE", DbType.DateTime, EndDate.AddHours(23.99));
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AuditTrailInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                }
                dr.Close();
            }
            return list;
        }

        public List<AuditTrailInfo> GetTableList()
        {
            List<AuditTrailInfo> list = new List<AuditTrailInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT TABLE_NAME FROM TBL_AUDIT GROUP BY TABLE_NAME ORDER BY TABLE_NAME");
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AuditTrailInfo(dr.GetString(0)));
                }
                dr.Close();
            }
            return list;
        }

        public int Save(AuditTrailInfo Info)
        {
            int status = 0;
            try
            {
                Database mydb = DBConnection.CreateDatabase();

                DbCommand mycomm = mydb.GetSqlStringCommand(@"
                            IF EXISTS (SELECT * FROM TBL_AUDIT WHERE ROW_ID = @ROW_ID)            
                       	    UPDATE TBL_AUDIT
                               SET [ACT_TYPE] = @ACT_TYPE
                                  ,[TABLE_NAME] = @TABLE_NAME
                                  ,[PRIMARY_KEY] = @PRIMARY_KEY
                                  ,[FIELD_NAME] = @FIELD_NAME
                                  ,[OLD_VALUE] = @OLD_VALUE
                                  ,[NEW_VALUE] = @NEW_VALUE
                                  ,[UPDATE_BY] = @UPDATE_BY
                                  ,[UPDATE_DATE] = @UPDATE_DATE
                            WHERE ROW_ID = @ROW_ID AND TABLE_NAME = @TABLE_NAME AND FIELD_NAME = @FIELD_NAME
                            ELSE  
                            INSERT INTO TBL_AUDIT(ACT_TYPE,TABLE_NAME,PRIMARY_KEY,FIELD_NAME,OLD_VALUE,NEW_VALUE,UPDATE_BY,UPDATE_DATE,DB_USER_NAME) 
                            VALUES(@ACT_TYPE,@TABLE_NAME,@PRIMARY_KEY,@FIELD_NAME,@OLD_VALUE,@NEW_VALUE,@UPDATE_BY,@UPDATE_DATE,system_user)");

                mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, Info.ACT_TYPE);
                mydb.AddInParameter(mycomm, "@TABLE_NAME", DbType.String, Info.TABLE_NAME);
                mydb.AddInParameter(mycomm, "@PRIMARY_KEY", DbType.String, Info.PRIMARY_KEY);
                mydb.AddInParameter(mycomm, "@FIELD_NAME", DbType.String, Info.FIELD_NAME);
                mydb.AddInParameter(mycomm, "@OLD_VALUE", DbType.DateTime, Info.OLD_VALUE);
                mydb.AddInParameter(mycomm, "@NEW_VALUE", DbType.String, Info.NEW_VALUE);
                mydb.AddInParameter(mycomm, "@UPDATE_BY", DbType.DateTime, Info.UPDATE_BY);
                mydb.AddInParameter(mycomm, "@UPDATE_DATE", DbType.String, Info.UPDATE_DATE);

                status = mydb.ExecuteNonQuery(mycomm);
            }
            catch (Exception) { }
            return status;
        }

        public int GetRowNumber(string TableName, DateTime StartDate, DateTime EndDate)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT count(0) AS jmlData FROM(
                            SELECT ACT_TYPE, TABLE_NAME, PRIMARY_KEY, FIELD_NAME, ISNULL(OLD_VALUE,'') AS OLD_VALUE, ISNULL(NEW_VALUE,'') AS NEW_VALUE, UPDATE_BY, UPDATE_DATE, DB_USER_NAME
                            FROM TBL_AUDIT WHERE (ACT_TYPE <> 'X') AND (TABLE_NAME LIKE '' + @PARAM + '') AND (UPDATE_DATE BETWEEN @START_DATE AND @END_DATE))
                            AS DATA";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            cmd.CommandTimeout = 1800;
            db.AddInParameter(cmd, "@PARAM", DbType.String, TableName);
            db.AddInParameter(cmd, "@START_DATE", DbType.DateTime, StartDate);
            db.AddInParameter(cmd, "@END_DATE", DbType.DateTime, EndDate.AddHours(23.99));
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<AuditTrailInfo> GetList(string TableName, DateTime StartDate, DateTime EndDate, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<AuditTrailInfo> list = new List<AuditTrailInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT  * FROM ( 
                              SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row,
                              ACT_TYPE, TABLE_NAME, PRIMARY_KEY, FIELD_NAME, ISNULL(OLD_VALUE,'') AS OLD_VALUE, ISNULL(NEW_VALUE,'') AS NEW_VALUE, UPDATE_BY, UPDATE_DATE, DB_USER_NAME 
                           FROM TBL_AUDIT WHERE (ACT_TYPE <> 'X') AND (TABLE_NAME = @PARAM) AND (UPDATE_DATE BETWEEN @START_DATE AND @END_DATE)
                              ) AS LogWithRowNumbers 
                           WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR ORDER BY UPDATE_DATE DESC";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            cmd.CommandTimeout = 1800;
            db.AddInParameter(cmd, "@PARAM", DbType.String, TableName);
            db.AddInParameter(cmd, "@START_DATE", DbType.DateTime, StartDate);
            db.AddInParameter(cmd, "@END_DATE", DbType.DateTime, EndDate.AddHours(23.99));
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AuditTrailInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9)));
                }
                dr.Close();
            }
            return list;
        }
    }
 }